package com.szholly.pro.watergas_server.ps.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.RowBase;
import com.szholly.plug.form.excel.utils.StringUtil;

@Service
public class QCSummaryEvaluationService {
	protected Logger logger = LoggerFactory.getLogger(getClass());
	
	/**
	 * 获取质控汇总评价信息(单独质控)
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	public List<RowBase> getSingleQCSumEvaDataList(Map<String, String> parm) throws Exception{
		//样品总数sql
		String sqlSampleCount = getSumEvaSampleCountSql(parm);
		//合格样品数sql
		String sqlQuaSampleCount = getSumEvaQuaSampleCountSql(parm);
		//总sql
		String sql="select t2.monitorparamname,count(*) as detailCount,min(t.exp_rd) as exp_rd_min,max(t.exp_rd) as exp_rd_max,min(t.exp_mvalue) as exp_mvalue_min,max(t.exp_mvalue) as exp_mvalue_max,"
				+ "sa.sampleCount,t3.mindetectionlimit,t3.mindetectionlimitunit,t4.quaSampleCount,(t4.QuaSampleCount/count(*)*100) as quaRate";
		sql	+=" from qc_expdetaildata t left join qc_expdata t1 on t1.exp_number = t.exp_number"
				+"   left join (select count(t1.monitorparam) as sampleCount,t1.monitorparam from qc_sampledata t"
				+" left join qc_sampledetaildata t1 on t.samplenumber=t1.samplenumber where 1 = 1";
				if(StringUtil.IsNotNullOrEmpty(parm.get("startDate"))){
					sql+=" and t.datatime>=to_date('"+parm.get("startDate")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";			
				}
				if(StringUtil.IsNotNullOrEmpty(parm.get("endDate"))){
					sql+=" and t.datatime<=to_date('"+parm.get("endDate")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";			
				}
				
		sql	+= " group by t1.monitorparam) sa on sa.monitorparam=t.EXP_PARAM "
				+ " left join (select t.*,t1.companycode from qc_monitorparam t left join qc_companyparaminfo t1"
				+ " on t.guid = t1.paramguid) t2 on t2.monitorparamcode = t.exp_param and t2.monitortypecode=t1.monitortypecode and t2.companycode=t1.qccompanycode" 
				+ " left join qc_expmethod t3 on t3.methodcode = t.exp_methodcode"
				+ " left join";
		sql+= "(" + sqlQuaSampleCount + ") t4 on t4.monitorparamcode = t2.monitorparamcode where 1=1";
		
		if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORINGTYPE"))){//监测类型		
			sql +=" and t1.monitortypecode in("+ParamPartition(parm.get("MONITORINGTYPE"))+")  ";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("MonitorMethod"))){//监测手段
			sql +=" and t3.companytype in("+ParamPartition(parm.get("MonitorMethod"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("CompanyCode"))){//公司分类
			sql +=" and t1.qccompanycode in("+ParamPartition(parm.get("CompanyCode"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("QCType"))){//质控类型
			sql +=" and t1.qctypecode in("+ParamPartition(parm.get("QCType"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORPARAMCODE"))){//监测项目
			sql +=" and t.exp_param in("+ParamPartition(parm.get("MONITORPARAMCODE"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("startDate"))){
			sql+=" and t1.datetime>=to_date('"+parm.get("startDate")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("endDate"))){
			sql+=" and t1.datetime<=to_date('"+parm.get("endDate")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";			
		}
		sql+=" group by t2.monitorparamname,t3.mindetectionlimit,t3.mindetectionlimitunit,t4.QuaSampleCount,sa.sampleCount"
				+" order by t2.monitorparamname";
		
		List<GlobalParameter> listParam = new ArrayList<>();
		List<RowBase> SingleQCList = DataUtil.getSelectRows(sql, listParam);
		return SingleQCList;
	}
	
	/**
	 * 获取质控汇总详情信息(单独质控)
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	public List<RowBase> getSingleQCSumEvaDetailDataList(Map<String, String> parm) throws Exception{		
		String QCType = parm.get("QCType");
		List<RowBase> reusltList = null;
		switch(QCType)
		{   
			case "1": //现场平行样（密码）
			case "2": //实验室平行样（明码）
			case "5": //实验室空白
			case "7": //实验室平行样（密码）
				reusltList = getSumEvaGroupDetailDataList(parm);
				break;
			case "0": //现场空白
			case "4": //标样（质控样）
				reusltList = getSumEvaSingleDetailDataList(parm);
				break;
			default:
				reusltList = getSumEvaGroupDetailDataList(parm);
				break;
		}		
		return reusltList;	
	}
	
	
	/**
	 * 获取质控汇总详情信息(适用于双样一组的质控类型)
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	public List<RowBase> getSumEvaGroupDetailDataList(Map<String, String> parm) throws Exception{		
		String sqldetail="select t9.reoss,t9.pmad,t9.srruplimit,t9.srrdownlimit,t7.exp_mvalue as exp_mvalue2,(t5.relatedexp_number||t6.relatedexp_number) as orderID,(t3.samplenumber||chr(10)||t8.samplenumber) as realSampleNumber,(t.exp_ssd_ssn||chr(10)||t7.exp_ssd_ssn) as sampleNumber,t2.monitorparamname,t.exp_mvalue,t.exp_rd from qc_expdetaildata t"
					+ " left join qc_expdata t1 on t1.exp_number = t.exp_number"
					+ " left join (select t.*,t1.companycode from qc_monitorparam t left join qc_companyparaminfo t1"
					+ " on t.guid = t1.paramguid) t2 on t2.monitorparamcode = t.exp_param and t2.monitortypecode=t1.monitortypecode and t2.companycode=t1.qccompanycode"
					+ " left join qc_sampledata t3 on t3.samplenumber = t1.samplenumber"
					+ " left join qc_expmethod t4 on t4.methodcode = t.exp_methodcode"
					+ " left join qc_exprelation t5 on t5.exp_number = t1.exp_number"       
					+ " left join qc_exprelation t6 on t6.relatedexp_number = t1.exp_number"
					+ " left join qc_expdetaildata t7 on t7.exp_number = t5.relatedexp_number and t7.exp_param = t.exp_param"
					+ " left join qc_expdata t8 on t8.exp_number = t7.exp_number"
					+ " left join qc_monitorparamdetail t9 on t2.guid = t9.paramguid and t.exp_sc >= t9.mcdownlimit and t.exp_sc < t9.mcuplimit"
					+ " where 1=1 and t5.relatedexp_number is not null";
		if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORINGTYPE"))){//监测类型		
			sqldetail +=" and t1.monitortypecode in("+ParamPartition(parm.get("MONITORINGTYPE"))+")  ";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("MonitorMethod"))){//监测手段
			sqldetail +=" and t4.companytype in("+ParamPartition(parm.get("MonitorMethod"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("CompanyCode"))){//公司分类
			sqldetail +=" and t1.qccompanycode in("+ParamPartition(parm.get("CompanyCode"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("QCType"))){//质控类型
			sqldetail +=" and t1.qctypecode in("+ParamPartition(parm.get("QCType"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORPARAMCODE"))){//监测项目
			sqldetail +=" and t.exp_param in("+ParamPartition(parm.get("MONITORPARAMCODE"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("startDate"))){
			sqldetail+=" and t1.datetime>=to_date('"+parm.get("startDate")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("endDate"))){
			sqldetail+=" and t1.datetime<=to_date('"+parm.get("endDate")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";			
		}	
		sqldetail+=" group by t2.monitorparamname,t.exp_mvalue,t.exp_rd,t.exp_ssd_ssn,t5.relatedexp_number,t6.relatedexp_number,t7.exp_mvalue,t7.exp_ssd_ssn,t3.samplenumber,t8.samplenumber,t9.reoss,t9.pmad,t9.srruplimit,t9.srrdownlimit"
				+ " order by t2.monitorparamname,orderID,t5.relatedexp_number";
		List<GlobalParameter> listParam = new ArrayList<>();
		List<RowBase> SampleDetailList = DataUtil.getSelectRows(sqldetail, listParam);
		return SampleDetailList;
	}
	
	/**
	 * 获取质控汇总详情信息(适用于单样一组的质控类型)
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	public List<RowBase> getSumEvaSingleDetailDataList(Map<String, String> parm) throws Exception{				
		String sqldetail="select t8.reoss,t8.pmad,t8.srruplimit,t8.srrdownlimit,t.exp_ssd_svalue,t.EXP_SSD_MVALUE_CS,t.EXP_SSD_QS_CS,t7.exp_mvalue as exp_mvalue2,(t5.exp_number||t6.exp_number) as orderID,t.EXP_SSD_SSN,t3.samplenumber,t2.monitorparamname,t.exp_mvalue,t.exp_rd from qc_expdetaildata t"
					+ " left join qc_expdata t1 on t1.exp_number = t.exp_number"
					+ " left join (select t.*,t1.companycode from qc_monitorparam t left join qc_companyparaminfo t1"
					+ " on t.guid = t1.paramguid) t2 on t2.monitorparamcode = t.exp_param and t2.monitortypecode=t1.monitortypecode and t2.companycode=t1.qccompanycode"
					+ " left join qc_sampledata t3 on t3.samplenumber = t1.samplenumber"
					+ " left join qc_expmethod t4 on t4.methodcode = t.exp_methodcode"
					+ " left join qc_exprelation t5 on t5.exp_number = t1.exp_number"       
					+ " left join qc_exprelation t6 on t6.relatedexp_number = t1.exp_number"
					+ " left join qc_expdetaildata t7 on t7.exp_number = t5.relatedexp_number and t7.exp_param = t.exp_param"
					+ " left join qc_monitorparamdetail t8 on t2.guid = t8.paramguid and t.exp_sc >= t8.mcdownlimit and t.exp_sc < t8.mcuplimit"
					+ " where 1=1 ";
		if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORINGTYPE"))){//监测类型		
			sqldetail +=" and t1.monitortypecode in("+ParamPartition(parm.get("MONITORINGTYPE"))+")  ";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("MonitorMethod"))){//监测手段
			sqldetail +=" and t4.companytype in("+ParamPartition(parm.get("MonitorMethod"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("CompanyCode"))){//公司分类
			sqldetail +=" and t1.qccompanycode in("+ParamPartition(parm.get("CompanyCode"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("QCType"))){//质控类型
			sqldetail +=" and t1.qctypecode in("+ParamPartition(parm.get("QCType"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORPARAMCODE"))){//监测项目
			sqldetail +=" and t.exp_param in("+ParamPartition(parm.get("MONITORPARAMCODE"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("startDate"))){
			sqldetail+=" and t1.datetime>=to_date('"+parm.get("startDate")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("endDate"))){
			sqldetail+=" and t1.datetime<=to_date('"+parm.get("endDate")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";			
		}	
		sqldetail+=" group by t2.monitorparamname,t.exp_mvalue,t.exp_rd,t.exp_ssd_ssn,t5.exp_number,t6.exp_number,t7.exp_mvalue,t7.exp_ssd_ssn,t3.samplenumber,t.exp_ssd_svalue,t.EXP_SSD_MVALUE_CS,t.EXP_SSD_QS_CS,t8.reoss,t8.pmad,t8.srruplimit,t8.srrdownlimit"
				+ " order by t2.monitorparamname,orderID,t5.exp_number,t.EXP_SSD_QS_CS";
		List<GlobalParameter> listParam = new ArrayList<>();
		List<RowBase> SampleDetailList = DataUtil.getSelectRows(sqldetail, listParam);
		return SampleDetailList;
		
	}
	
	/**
	 * 获取质控汇总评价信息(总体)
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	public List<RowBase> getSummaryEvaluationDataList(Map<String, String> parm) throws Exception{
		//样品总数sql
		String sqlSampleCount = getSumEvaSampleCountSql(parm);
		//合格样品数sql
		String sqlSumEvaByQCType;
		//总sql
		String sql="select t2.monitorparamname, sa.sampleCount";
		sql+= " ,t3.detailCount as FBCount,t3.QuaRate as FBQuaRate"
				+ " ,t4.detailCount as EBCount,t4.QuaRate as EBQuaRate"
				+ " ,t5.detailCount as PLCCount,t5.exp_rd_min as PLCEXP_RD_MIN,t5.exp_rd_max as PLCEXP_RD_MAX,t5.QuaSampleCount as PLCQuaSampleCount,t5.QuaRate as PLCQuaRate"
				+ " ,t6.detailCount as FCPCount,t6.exp_rd_min as FCPEXP_RD_MIN,t6.exp_rd_max as FCPEXP_RD_MAX,t6.QuaSampleCount as FCPQuaSampleCount,t6.QuaRate as FCPQuaRate"
				+ " ,t7.detailCount as SRCount,t7.exp_mvalue_min as SREXP_MVALUE_MIN,t7.exp_mvalue_max as SREXP_MVALUE_MAX,t7.QuaSampleCount as SRQuaSampleCount,t7.QuaRate as SRQuaRate"
				+ " ,t8.detailCount as SMSSCount,t8.QuaSampleCount as SMSSQuaSampleCount,t8.QuaRate as SMSSQuaRate"
				+ " ,t9.detailCount as CSCount,t9.QuaRate as CSRate";
		sql+= " from qc_expdetaildata t left join (select count(te.monitorparam ) as sampleCount,te.monitorparam from qc_sampledata ta"
				+" left join qc_sampledetaildata te on te.samplenumber=ta.samplenumber where 1 = 1";
				if(StringUtil.IsNotNullOrEmpty(parm.get("startDate"))){
					sql+=" and ta.datatime>=to_date('"+parm.get("startDate")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";			
				}
				if(StringUtil.IsNotNullOrEmpty(parm.get("endDate"))){
					sql+=" and ta.datatime<=to_date('"+parm.get("endDate")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";			
				}
			sql+="  group by te.monitorparam ) sa on sa.monitorparam= t.exp_param"
				+ " left join qc_expdata t1 on t1.exp_number = t.exp_number"
				+ " left join (select t.*,t1.companycode from qc_monitorparam t left join qc_companyparaminfo t1"
				+ " on t.guid = t1.paramguid) t2 on t2.monitorparamcode = t.exp_param and t2.monitortypecode=t1.monitortypecode and t2.companycode=t1.qccompanycode";
		
		//现场空白
		parm.put("QCType", "0");
		sqlSumEvaByQCType = getSumEvaSqlByQCType(parm);
		sql+= " left join(" + sqlSumEvaByQCType + ") t3 on t3.monitorparamcode=t2.monitorparamcode ";
		parm.put("QCType", "5");
		sqlSumEvaByQCType = getSumEvaSqlByQCType(parm);
		sql+= " left join(" + sqlSumEvaByQCType + ") t4 on t4.monitorparamcode=t2.monitorparamcode ";
		parm.put("QCType", "2");
		sqlSumEvaByQCType = getSumEvaSqlByQCType(parm);
		sql+= " left join(" + sqlSumEvaByQCType + ") t5 on t5.monitorparamcode=t2.monitorparamcode ";
		parm.put("QCType", "1");
		sqlSumEvaByQCType = getSumEvaSqlByQCType(parm);
		sql+= " left join(" + sqlSumEvaByQCType + ") t6 on t6.monitorparamcode=t2.monitorparamcode ";
		parm.put("QCType", "3");
		sqlSumEvaByQCType = getSumEvaSqlByQCType(parm);
		sql+= " left join(" + sqlSumEvaByQCType + ") t7 on t7.monitorparamcode=t2.monitorparamcode ";
		//质控样 自配标样  标样是否有证 0：否 1：是
		parm.put("QCType", "4");
		parm.put("ISCS", "0");
		sqlSumEvaByQCType = getSumEvaSqlByQCType(parm);
		sql+= " left join(" + sqlSumEvaByQCType + ") t8 on t8.monitorparamcode=t2.monitorparamcode ";
		//质控样 有证标样
		parm.put("QCType", "4");
		parm.put("ISCS", "1");
		sqlSumEvaByQCType = getSumEvaSqlByQCType(parm);
		sql+= " left join(" + sqlSumEvaByQCType + ") t9 on t9.monitorparamcode=t2.monitorparamcode where 1=1";
		
		if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORINGTYPE"))){//监测类型		
			sql +=" and t1.monitortypecode in("+ParamPartition(parm.get("MONITORINGTYPE"))+")  ";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("CompanyCode"))){//公司分类
			sql +=" and t1.qccompanycode in("+ParamPartition(parm.get("CompanyCode"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORPARAMCODE"))){//监测项目
			sql +=" and t.exp_param in("+ParamPartition(parm.get("MONITORPARAMCODE"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("startDate"))){
			sql+=" and t1.datetime>=to_date('"+parm.get("startDate")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("endDate"))){
			sql+=" and t1.datetime<=to_date('"+parm.get("endDate")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";
		}
		
		sql+= " group by t2.monitorparamname,t3.detailCount,t3.QuaRate,t4.detailCount,t4.QuaRate"
				+ ",t5.detailCount,t5.exp_rd_min,t5.exp_rd_max,t5.QuaSampleCount,t5.QuaRate"
				+ ",t6.detailCount,t6.exp_rd_min,t6.exp_rd_max,t6.QuaSampleCount,t6.QuaRate"
				+ ",t7.detailCount,t7.exp_mvalue_min,t7.exp_mvalue_max,t7.QuaSampleCount,t7.QuaRate"
				+ ",t8.detailCount,t8.QuaSampleCount,t8.QuaRate"
				+ ",t9.detailCount,t9.QuaRate,sa.sampleCount";
		sql+=" order by t2.monitorparamname";
		List<GlobalParameter> listParam = new ArrayList<>();
		List<RowBase> SumEvaList = DataUtil.getSelectRows(sql, listParam);
		return SumEvaList;
	}
	
	/**
	 * 获取质控汇总评价信息-按不同的质控类型，获得各自的汇总评价信息sql
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	public String getSumEvaSqlByQCType(Map<String, String> parm){
		//合格样品数sql
		String sqlQuaSampleCount = getSumEvaQuaSampleCountSql(parm);	
		//单独质控类型的汇总信息
		String sqlSumEvaByQCType = "select count(*) as detailCount,t2.monitorparamcode,t2.monitorparamname,t4.QuaSampleCount,(t4.QuaSampleCount/count(*)*100) as QuaRate"
				+ " ,min(t.exp_rd) as exp_rd_min,max(t.exp_rd) as exp_rd_max,min(t.exp_mvalue) as exp_mvalue_min,max(t.exp_mvalue) as exp_mvalue_max"
				+ " from qc_expdetaildata t left join qc_expdata t1 on t1.exp_number = t.exp_number"
				+ " left join (select t.*,t1.companycode from qc_monitorparam t left join qc_companyparaminfo t1"
				+ " on t.guid = t1.paramguid) t2 on t2.monitorparamcode = t.exp_param and t2.monitortypecode=t1.monitortypecode and t2.companycode=t1.qccompanycode left join";
		sqlSumEvaByQCType += "(" +  sqlQuaSampleCount + ")";
		sqlSumEvaByQCType += " t4 on t4.monitorparamcode = t2.monitorparamcode where 1=1";
		if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORINGTYPE"))){//监测类型		
			sqlSumEvaByQCType +=" and t1.monitortypecode in("+ParamPartition(parm.get("MONITORINGTYPE"))+")  ";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("CompanyCode"))){//公司分类
			sqlSumEvaByQCType +=" and t1.qccompanycode in("+ParamPartition(parm.get("CompanyCode"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("QCType"))){//质控类型
			sqlSumEvaByQCType +=" and t1.qctypecode in("+ParamPartition(parm.get("QCType"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORPARAMCODE"))){//监测项目
			sqlSumEvaByQCType +=" and t.exp_param in("+ParamPartition(parm.get("MONITORPARAMCODE"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("startDate"))){
			sqlSumEvaByQCType+=" and t1.datetime>=to_date('"+parm.get("startDate")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("endDate"))){
			sqlSumEvaByQCType+=" and t1.datetime<=to_date('"+parm.get("endDate")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";
		}
		//自配标样
		if(StringUtil.IsNotNullOrEmpty(parm.get("ISCS"))&&"0".equals(parm.get("ISCS"))){
			sqlSumEvaByQCType+=" and t.EXP_SSD_MVALUE_CS is null";
		}
		//有证标样
		if(StringUtil.IsNotNullOrEmpty(parm.get("ISCS"))&&"1".equals(parm.get("ISCS"))){
			sqlSumEvaByQCType+=" and t.EXP_SSD_MVALUE_CS is not null";
		}
		sqlSumEvaByQCType+=" group by t2.monitorparamcode,t2.monitorparamname,t4.QuaSampleCount";
		return sqlSumEvaByQCType;
	}
	
	/**
	 * 获取质控汇总评价信息-获取样品总数sql
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	public String getSumEvaSampleCountSql(Map<String, String> parm){
		//样品总数sql
		String sqlSampleCount="select count(*) from qc_sampledata t where 1=1";
		if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORINGTYPE"))){//监测类型		
			sqlSampleCount +=" and t.MONITORTYPE in("+ParamPartition(parm.get("MONITORINGTYPE"))+")  ";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("CompanyCode"))){//公司分类
			sqlSampleCount +=" and t.COMPANY in("+ParamPartition(parm.get("CompanyCode"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("startDate"))){
			sqlSampleCount+=" and t.datatime>=to_date('"+parm.get("startDate")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("endDate"))){
			sqlSampleCount+=" and t.datatime<=to_date('"+parm.get("endDate")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";			
		}
		return sqlSampleCount;
	}
	
	/**
	 * 获取质控汇总评价信息-获取合格样品数sql
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	public String getSumEvaQuaSampleCountSql(Map<String, String> parm){
		//合格样品数sql
		//--按参数分组查出各自的合格数
		String sqlQuaSampleCount ="select t2.monitorparamcode,t2.monitorparamname,count(*) as QuaSampleCount from qc_expdetaildata t"
				+ " left join qc_expdata t1 on t1.exp_number = t.exp_number"
				+ " left join (select t.*,t1.companycode from qc_monitorparam t left join qc_companyparaminfo t1"
				+ " on t.guid = t1.paramguid) t2 on t2.monitorparamcode = t.exp_param and t2.monitortypecode=t1.monitortypecode and t2.companycode=t1.qccompanycode"
				+ " left join qc_expmethod t3 on t3.methodcode = t.exp_methodcode"
				+ " left join qc_monitorparamdetail t4 on t2.guid = t4.paramguid and t.exp_sc >= t4.mcdownlimit and t.exp_sc < t4.mcuplimit";
		//根据质控类型不同，判断合格的标准也不同
		switch(parm.get("QCType"))
		{   
			case "1": //现场平行样（密码）
			case "2": //实验室平行样（明码）
			case "7": //实验室平行样（密码）
				sqlQuaSampleCount +=" where t.EXP_RD <= t4.pmad  ";
				break;
			case "0": //现场空白
			case "5": //实验室空白
				sqlQuaSampleCount +=" where t.EXP_MVALUE <= t3.mindetectionlimit ";
				break;
			case "3": //加标回收
				sqlQuaSampleCount +=" where t.EXP_MVALUE >= t4.srrdownlimit and t.EXP_MVALUE <= t4.srruplimit ";
				break;
			case "4": //标样（质控样）
				sqlQuaSampleCount +=" where ((t.EXP_SSD_MVALUE_CS is null and abs(((t.EXP_MVALUE-t.EXP_SSD_SVALUE)/t.EXP_SSD_SVALUE)*100) <= t4.REOSS)"
						+ " or (t.EXP_SSD_MVALUE_CS is not null and (abs((t.EXP_MVALUE+t.EXP_SSD_MVALUE_CS)/2)-EXP_SSD_SVALUE) <= t.EXP_SSD_QS_CS))";
				break;
			default:
				sqlQuaSampleCount +=" where t.EXP_MVALUE <= t3.mindetectionlimit ";
				break;
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORINGTYPE"))){//监测类型		
			sqlQuaSampleCount +=" and t1.monitortypecode in("+ParamPartition(parm.get("MONITORINGTYPE"))+")  ";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("CompanyCode"))){//公司分类
			sqlQuaSampleCount +=" and t1.qccompanycode in("+ParamPartition(parm.get("CompanyCode"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("QCType"))){//质控类型
			sqlQuaSampleCount +=" and t1.qctypecode in("+ParamPartition(parm.get("QCType"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORPARAMCODE"))){//监测项目
			sqlQuaSampleCount +=" and t.exp_param in("+ParamPartition(parm.get("MONITORPARAMCODE"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("startDate"))){
			sqlQuaSampleCount+=" and t1.datetime>=to_date('"+parm.get("startDate")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("endDate"))){
			sqlQuaSampleCount+=" and t1.datetime<=to_date('"+parm.get("endDate")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";			
		}
		//自配标样
		if(StringUtil.IsNotNullOrEmpty(parm.get("ISCS"))&&"0".equals(parm.get("ISCS"))){
			sqlQuaSampleCount+=" and t.EXP_SSD_MVALUE_CS is null";
		}
		//有证标样
		if(StringUtil.IsNotNullOrEmpty(parm.get("ISCS"))&&"1".equals(parm.get("ISCS"))){
			sqlQuaSampleCount+=" and t.EXP_SSD_MVALUE_CS is not null";
		}
		sqlQuaSampleCount +=" group by t2.monitorparamname,t2.monitorparamcode"
				+ " order by t2.monitorparamname";
		return sqlQuaSampleCount;
	}
	
	public String ParamPartition(String paramData){
		String[] param=paramData.split(",");
		String StrParam="";
		for(int i=0;i<param.length;i++){
			if(param.length-i==1){
				StrParam+="'"+param[i]+"'";
			}else{
				StrParam+="'"+param[i]+"',";
			}			
		}
		return StrParam;
	}	
}
